package com.cms.sync.service;

import java.util.ArrayList;
import java.util.List;

import com.cms.model.ImportData;
import com.cms.sync.model.KpayKeep;
import com.cms.sync.model.KpayKeepAndUserNum;
import com.cms.sync.model.KpayUserNum;
import com.model.Admin;
import com.service.AbstractDateService;
import com.utils.dbcp.ConnectionManager;
import com.utils.dbcp.ModelGenerator;

public class KpayKeepServiceImpl extends AbstractDateService implements KpayKeepService {

	@Override
	public List<KpayKeep> getAllKpayKeepByDay(int day) {
		String sql = "select * from t_kpaykeep where day>="+day;
		ModelGenerator<KpayKeep> model = new ModelGenerator<KpayKeep>(KpayKeep.class);
		return model.generateList(base,sql.toString());
	}

	@Override
	public List<KpayKeepAndUserNum> getkeepandusernum(String startday,
			String endday, KpayKeepAndUserNum model, String dimension,Admin admin) {
		List<String> fl = new ArrayList<String>();
		String ch="";
		if(checkQueryDimension(dimension, "day")){
			ch+="t.day day,";
			fl.add("t.day");
		}
		if(checkQueryDimension(dimension, "channel")){
			ch+="t.chid chid,t.channelid channelid,";
			fl.add("t.chid,t.channelid");
		}
		if(checkQueryDimension(dimension, "app")){
			ch+="t.appversionnumber appversionnumber,";
			fl.add("t.appversionnumber");
		}
		if(checkQueryDimension(dimension, "operator")){
			ch+="t.operator operator,t.operatorname operatorname,";
			fl.add("t.operator,t.operatorname");
		}
		
		/*String sql = "select "+ch+" sum(t.nextdaykeep) nextdaykeep,sum(t.twodaykeep) twodaykeep,sum(t.threedaykeep) threedaykeep,sum(t.fourdaykeep) fourdaykeep,sum(t.fivedaykeep) fivedaykeep,sum(t.sixdaykeep) sixdaykeep,sum(t.sevendaykeep) sevendaykeep,sum(t.tendaykeep) tendaykeep,sum(t.fifteendaykeep) fifteendaykeep,sum(t.thirtydaykeep) thirtydaykeep,sum(t.newuser) newuser,sum(t.activeuser) activeuser from (" +
				"select a.day day,a.channelid channelid,a.chid chid,a.appversionnumber appversionnumber,a.nextdaykeep nextdaykeep,a.twodaykeep twodaykeep,a.threedaykeep threedaykeep,a.fourdaykeep fourdaykeep,a.fivedaykeep fivedaykeep,a.sixdaykeep sixdaykeep,a.sevendaykeep sevendaykeep,a.tendaykeep tendaykeep,a.fifteendaykeep fifteendaykeep,a.thirtydaykeep thirtydaykeep,b.newuser newuser,b.activeuser activeuser " +
				"from (select day,channelid,chid,appversionnumber,sum(nextdaykeep) nextdaykeep,sum(twodaykeep) twodaykeep,sum(threedaykeep) threedaykeep,sum(fourdaykeep) fourdaykeep,sum(fivedaykeep) fivedaykeep,sum(sixdaykeep) sixdaykeep,sum(sevendaykeep) sevendaykeep,sum(tendaykeep) tendaykeep,sum(fifteendaykeep) fifteendaykeep,sum(thirtydaykeep) thirtydaykeep from t_kpaykeep where day>="+startday+" and day<="+endday+" group by day,channelid,chid,appversionnumber) a,t_kpayusernum b where a.chid=b.chid and a.channelid=b.channelid and a.appversionnumber=b.appversionnumber and a.day=b.day) t ";
		*/
		String sql1 = "select "+ch+" sum(t.nextdaykeep) nextdaykeep,sum(t.twodaykeep) twodaykeep,sum(t.threedaykeep) threedaykeep,sum(t.fourdaykeep) fourdaykeep,sum(t.fivedaykeep) fivedaykeep,sum(t.sixdaykeep) sixdaykeep,sum(t.sevendaykeep) sevendaykeep,sum(t.tendaykeep) tendaykeep,sum(t.fifteendaykeep) fifteendaykeep,sum(t.thirtydaykeep) thirtydaykeep,sum(a.newuser) newuser,sum(a.activeuser) activeuser " +
				"from (select day,channelid,chid,appversionnumber,operator,operatorname,sum(nextdaykeep) nextdaykeep,sum(twodaykeep) twodaykeep,sum(threedaykeep) threedaykeep,sum(fourdaykeep) fourdaykeep,sum(fivedaykeep) fivedaykeep,sum(sixdaykeep) sixdaykeep,sum(sevendaykeep) sevendaykeep,sum(tendaykeep) tendaykeep,sum(fifteendaykeep) fifteendaykeep,sum(thirtydaykeep) thirtydaykeep from t_kpaykeep where day>="+startday+" and day<="+endday+" group by day,channelid,chid,appversionnumber,operator,operatorname) t " +
						"inner join t_kpayusernum a on (t.day=a.day and t.channelid=a.channelid and t.chid=a.chid and t.appversionnumber=a.appversionnumber and t.operator=a.operator and t.operatorname=a.operatorname) ";
		String w=" where 1=1 ";
		if(model.getChid()!=null&&!"".equals(model.getChid())){
			sql1+=" left join t_channel ch on ch.id=t.channelid ";
			w+=" and (t.chid like '%"+model.getChid()+"%' or ch.name like '%"+model.getChid()+"%')";
		}
		if(model.getAppversionnumber()!=null&&!"".equals(model.getAppversionnumber())){
			sql1+=" left join t_appversioninfo av on t.appversionnumber=av.appversionnumber ";
			w+=" and av.appname like '%"+model.getAppversionnumber()+"%'";
		}
		if(model.getOperator()!=null&&!"".equals(model.getOperator())){
			w+=" and t.operator='"+model.getOperator()+"'";
		}
		if(!"true".equals(admin.getIsAdmin())){
			w+=" and t.channelid in (select channelid from t_adminchannel where adminid="+admin.getId()+")";
		}
		sql1+=w+" group by "+fl.toString().replace("[", "").replace("]", "").replace(" ", "");
		
		logger.info("[sql] [method:getkeepandusernum] ["+sql1+"]");
		ModelGenerator<KpayKeepAndUserNum> m = new ModelGenerator<KpayKeepAndUserNum>(KpayKeepAndUserNum.class);
		return m.generateList(base,sql1.toString());
	}

	@Override
	public List<ImportData> queryGatherDataByContent(String startdate,
			String enddate, String appnumber, String appversionnumber,
			int channelid, String operator, String source, String province,
			String dimension,Admin admin) {

		String sql = "";
		{
			String gb = "";
			List<String> fl = new ArrayList<String>();
			
			if (!checkQueryDimension(dimension, "day"))
				gb += "0 ";
			else
				fl.add("day");
			gb += "day,";
			
			if (!checkQueryDimension(dimension, "appid"))
				gb += "'' ";
			else
				fl.add("appnumber");
			gb += "appnumber,";
			
			if (!checkQueryDimension(dimension, "appvid"))
				gb += "'' ";
			else
				fl.add("appversionnumber");
			gb += "appversionnumber,";
			
			if (!checkQueryDimension(dimension, "cpid"))
				gb += "-1 ";
			else
				fl.add("cpid");
			gb += "cpid,";
			
			if (!checkQueryDimension(dimension, "channelid"))
				gb += "-1 ";
			else
				fl.add("channelid");
			gb += "channelid,";
			
			if (!checkQueryDimension(dimension, "bd"))
				gb += "'' ";
			else
				fl.add("bd");
			gb += "bd,";
			
			if (!checkQueryDimension(dimension, "goodsname"))
				gb += "'' ";
			else
				fl.add("goodsname");
			gb += "goodsname,";
			
			if (!checkQueryDimension(dimension, "operator"))
				gb += "'' ";
			else
				fl.add("operator");
			gb += "operator,";
			
			if (!checkQueryDimension(dimension, "source"))
				gb += "'' ";
			else
				fl.add("source");
			gb += "source,";
									
			if (!checkQueryDimension(dimension, "province"))
				gb += "'' ";
			else
				fl.add("province");
			gb += "province,";
			
			sql = "select 0 id,0 datafileid," + gb + "fee,newusers,activeuser,payingcustomers,paidnumber " +
				"from (select " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") +
				",sum(fee) fee,sum(newuser) newusers,sum(activeuser) activeuser,sum(payuser) payingcustomers,sum(paynum) paidnumber from t_kpayusernum t1 " +
				"where (day>=" + startdate.replace("-", "")  + " " +
				"and day<=" + enddate.replace("-", "") + ") ";
			
			 if (appnumber != null && !appnumber.equals(""))
					sql += "and appnumber='" + appnumber +"' ";
			 if (appversionnumber != null && !appversionnumber.equals(""))
					sql += "and appversionnumber='" + appversionnumber +"' ";
			if (channelid > 0)
				sql += " and channelid='" + channelid + "' ";
			
			if (operator != null && !operator.equals(""))
				sql += "and operator='" + operator +"' ";
			
			if (source != null && !source.equals(""))
				sql += "and source='" + source +"' ";
			
			if (province != null && !province.equals(""))
				sql += "and province='" + province +"' ";
			if(!"true".equals(admin.getIsAdmin())){
				sql+=" and channelid in (select channelid from t_adminchannel where adminid="+admin.getId()+")";
			}
			
			sql += "group by " + fl.toString().replace("[", "").replace("]", "").replace(" ", "") + ") a " +
					"order by day,appnumber,channelid,operator,source";
		}
		logger.info("[Sql] [method:queryGatherDataByContent] ["+sql+"]");
		ModelGenerator<ImportData> dbcpmg = new ModelGenerator<ImportData>(ImportData.class);
		return dbcpmg.generateList(base, sql);
	
	}

}
